package com.example.memoapp.database;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class MemoDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "memo.db";
    private static final int DATABASE_VERSION = 1;

    public static final String TABLE_MEMOS = "memos";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_TITLE = "title";
    public static final String COLUMN_CONTENT = "content";
    public static final String COLUMN_DATE = "date";

    private static final String CREATE_TABLE_MEMOS =
            "CREATE TABLE " + TABLE_MEMOS + " (" +
                    COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_TITLE + " TEXT NOT NULL, " +
                    COLUMN_CONTENT + " TEXT, " +
                    COLUMN_DATE + " TEXT NOT NULL);";

    public MemoDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_MEMOS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MEMOS);
        onCreate(db);
    }

    public long addMemo(String title, String content, String date) {
        SQLiteDatabase db = this.getWritableDatabase();
        SQLiteStatement stmt = db.compileStatement(
                "INSERT INTO " + TABLE_MEMOS + " (" +
                        COLUMN_TITLE + ", " + COLUMN_CONTENT + ", " + COLUMN_DATE + ") VALUES (?, ?, ?);"
        );
        stmt.bindString(1, title);
        stmt.bindString(2, content);
        stmt.bindString(3, date);
        return stmt.executeInsert();
    }

    public Cursor getAllMemos() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(
                TABLE_MEMOS,
                null,
                null,
                null,
                null,
                null,
                COLUMN_DATE + " DESC"
        );
    }

    public Cursor getMemoById(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        String selection = COLUMN_ID + " = ?";
        String[] selectionArgs = { String.valueOf(id) };
        return db.query(
                TABLE_MEMOS,
                null,
                selection,
                selectionArgs,
                null,
                null,
                null
        );
    }

    public int updateMemo(int id, String title, String content, String date) {
        SQLiteDatabase db = this.getWritableDatabase();
        String whereClause = COLUMN_ID + " = ?";
        String[] whereArgs = { String.valueOf(id) };

        SQLiteStatement stmt = db.compileStatement(
                "UPDATE " + TABLE_MEMOS + " SET " +
                        COLUMN_TITLE + " = ?, " +
                        COLUMN_CONTENT + " = ?, " +
                        COLUMN_DATE + " = ? " +
                        "WHERE " + COLUMN_ID + " = ?;"
        );
        stmt.bindString(1, title);
        stmt.bindString(2, content);
        stmt.bindString(3, date);
        stmt.bindLong(4, id);

        return stmt.executeUpdateDelete();
    }

    public void deleteMemo(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        String whereClause = COLUMN_ID + " = ?";
        String[] whereArgs = { String.valueOf(id) };
        db.delete(TABLE_MEMOS, whereClause, whereArgs);
    }
}